﻿/*
 * Created by SharpDevelop.
 * User: CeCi
 * Date: 29/03/2014
 * Time: 07:13 p.m.
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using Gestión_UTN.Entities;
using System.Collections.Generic;

namespace Gestión_UTN.daimpl
{
	/// <summary>
	/// Description of telefonoAlumnodaimpl.
	/// </summary>
	public class telefonoAlumnodaimpl:Gestión_UTN.da.telefonoAlumnoda
	{
		public telefonoAlumnodaimpl()
		{
		}
		
		public bool SaveTelefonoAlumno(int alu_dni, int numero)
		{
			bool ingresado = false;
			
			OleDbConnection conex = null;
			OleDbTransaction trans = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				trans = null;
				
				if (null != conex) {
					
					string sql = "INSERT INTO tea_telefonosAlumnos(alu_dni, tea_numero)" +
                    			"VALUES(@alu_dni, @tea_numero)";
					
					OleDbCommand insertar = new OleDbCommand(sql,conex);
					
					conex.Open();
					trans = conex.BeginTransaction();
					
					object[] values = {alu_dni,numero};
					
					insertar.Parameters.AddRange(values);
					
					insertar.ExecuteNonQuery();
					
					trans.Commit();
					
					ingresado = true;
					
					conex.Close();
				}
				
			} catch (Exception) {
				ingresado = false;
				trans.Rollback();
				conex.Close();
			}
			
			return ingresado;
		}
		
		public bool DeleteTelefonoAlumno(int alu_dni, int numero)
		{
			bool eliminado = false;
			
			OleDbConnection conex = null;
			OleDbTransaction trans = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				trans = null;
				
				if (null != conex) {
					
					string sql = "DELETE FROM tea_telefonosAlumnos " +
						"WHERE alu_dni = @alu_dni AND tea_numero = @numero";
					
					OleDbCommand eliminar = new OleDbCommand(sql,conex);
					
					conex.Open();
					
					trans = conex.BeginTransaction();
					
					eliminar.Parameters.AddWithValue("@alu_dni",alu_dni);
					eliminar.Parameters.AddWithValue("@numero",numero);
					
					eliminar.ExecuteNonQuery();
					
					trans.Commit();
					
					eliminado = true;
					
					conex.Close();
				}
				
			} catch (Exception) {
				eliminado = false;
				trans.Rollback();
				conex.Close();
			}
			
			return eliminado;
		}
		
		public bool ModifyTelefonoAlumno(int alu_dni_anterior, int numero_anterior, int alu_dni, int numero)
		{
			bool modificado = false;
			
			OleDbConnection conex = null;
			OleDbTransaction trans = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				trans = null;
				
				if (null != conex) {
					
					string sql = "UPDATE tea_telefonosAlumnos SET alu_dni = @alu_dni," +
									" tea_numero = @numero " +
									"WHERE alu_dni = @alu_dni_anterior AND tea_numero = @numero_anterior";
					
					OleDbCommand modificar = new OleDbCommand(sql,conex);
					
					conex.Open();
					trans = conex.BeginTransaction();
					
					object[] values = {alu_dni,numero,alu_dni_anterior,numero_anterior};
					
					modificar.Parameters.AddRange(values);
					
					modificar.ExecuteNonQuery();
					
					trans.Commit();
					
					modificado = true;
					
					conex.Close();
				}
				
			} catch (Exception) {
				modificado = false;
				trans.Rollback();
				conex.Close();
			}
			
			return modificado;
		}
		
		public System.Data.DataTable FindAllTelefonoAlumnos()
		{
			DataTable tablaTelAlum = null;
			
			OleDbConnection conex = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				tablaTelAlum = new DataTable();
				
				if (null != conex) {
					
					string sql = "SELECT alu_dni, alu_nombre, alu_apellido, tea_numero" +
									" FROM tea_telefonosAlumnos tea, alu_alumnos alu " +
									"WHERE tea.alu_dni = alu.alu_dni";
					
					 OleDbDataAdapter adaptador = new OleDbDataAdapter(sql, conex);
					 
					 conex.Open();
					 
					 adaptador.Fill(tablaTelAlum,"telefonosAlumnos");
					
					 conex.Close();
				}
				
			} catch (Exception) {
				
				tablaTelAlum = null;
				conex.Close();
			}
			
			return tablaTelAlum;
		}
		
		public List<Gestión_UTN.Entities.TelefonoAlumno> FindTelefonoByDni(int alu_dni)
		{
			List<TelefonoAlumno> listaTelefonos = null;
			
			OleDbConnection conex = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				listaTelefonos = new List<TelefonoAlumno>();
				
				if (null != conex) {
					
					string sql = "SELECT alu_dni, tea_numero" +
									" FROM tea_telefonosAlumnos tea " +
									"WHERE alu_dni = @alu_dni";
					
					OleDbCommand consulta = new OleDbCommand(sql,conex);
					
					conex.Open();
					
					consulta.Parameters.AddWithValue("@alu_dni",alu_dni);
					
					OleDbDataReader lector = consulta.ExecuteReader();
					
					while (lector.Read()) 
					{
						TelefonoAlumno tel = new TelefonoAlumno(Convert.ToInt16(lector[0].ToString()),
						                                        Convert.ToInt16(lector[1].ToString()));
						listaTelefonos.Add(tel);
						
						tel = null;
					}
					
					conex.Close();
				}
				
			} catch (Exception) {
				
				listaTelefonos = null;
				conex.Close();
			}
			
			return listaTelefonos;
		}
	}
}
